package org.mcjug.locationmailer.db;

import java.util.Date;

import org.mcjug.locationmailer.R;
import org.mcjug.locationmailer.model.StatusMessage;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.provider.BaseColumns;

public class StatusMessageDatabaseHelper {
	private static final String TAG = StatusMessageDatabaseHelper.class.getSimpleName();

	private static final String DATABASE_NAME = "location.db";
	private static final String TABLE_NAME = "statusMessage";

	// Status Message column names
	public static final String C_ID = BaseColumns._ID;
	public static final String C_LATITUDE= "latitude";
	public static final String C_LONGITUDE = "longitude";
	public static final String C_MESSAGE = "message";
	public static final String C_ADDRESS = "address";
	public static final String C_CREATED_DATE = "created_date";

	private static final String ORDER_BY_CREATED_DATE = C_CREATED_DATE + " DESC";

	private int dbVersion;
	private final DbHelper dbHelper;

	public StatusMessageDatabaseHelper(Context context) {
		dbVersion = context.getResources().getInteger(R.integer.dbVersion);
		this.dbHelper = new DbHelper(context);
	}

	public void insertOrIgnore(StatusMessage statusMessage) {
		ContentValues values = getContentValues(statusMessage);	
		SQLiteDatabase db = this.dbHelper.getWritableDatabase();
		try {
    		db.insertWithOnConflict(TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_IGNORE);
		} finally {
			db.close();
		}
	}

	public long insertOrThrow(StatusMessage statusMessage) {
		long id = -1;
		ContentValues values = getContentValues(statusMessage);	
		SQLiteDatabase db = this.dbHelper.getWritableDatabase();
		try {
			id = db.insertOrThrow(TABLE_NAME, null, values);
		} finally {
			db.close();
		}
		return id;
	}

	public ContentValues getContentValues(StatusMessage statusMessage) {
		ContentValues values = new ContentValues();  
		Location location = statusMessage.getLocation();
		if (location == null) {
			values.putNull(C_LATITUDE);
			values.putNull(C_LONGITUDE);
		} else {
			values.put(C_LATITUDE, String.valueOf(location.getLatitude()));
			values.put(C_LONGITUDE, String.valueOf(location.getLongitude()));
		}
		
		values.put(C_MESSAGE, statusMessage.getMessage());
		values.put(C_ADDRESS, statusMessage.getAddress());
		values.put(C_CREATED_DATE, statusMessage.getCreatedDate().getTime());
		
		return values;
	}
	
	public Cursor getStatusMessages(int limitNum) {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();

		// Return cursor where columns are
		return db.query(TABLE_NAME, null, null, null, null, null, ORDER_BY_CREATED_DATE + " Limit " + limitNum);
	}    

	public StatusMessage getLatestStatusMessage() {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		StatusMessage statusMessage = null;
		try {
			Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, ORDER_BY_CREATED_DATE + " Limit 1");
			try {
				if (cursor.moveToFirst()) {
					statusMessage = new StatusMessage();
					statusMessage.setId(cursor.getLong(cursor.getColumnIndex(C_ID)));
					
					String latitude = String.format("%.3f", cursor.getString(cursor.getColumnIndex(C_LATITUDE)));
					String longitude = String.format("%.3f",cursor.getString(cursor.getColumnIndex(C_LONGITUDE)));
					
					Location location = new Location("");
					location.setLatitude(Double.parseDouble(latitude));
					location.setLongitude(Double.parseDouble(longitude));
					
					statusMessage.setLocation(location);
					statusMessage.setMessage(cursor.getString(cursor.getColumnIndex(C_MESSAGE)));
					statusMessage.setAddress(cursor.getString(cursor.getColumnIndex(C_ADDRESS)));
					statusMessage.setCreatedDate(new Date(cursor.getLong(cursor.getColumnIndex(C_CREATED_DATE))));
				}
			} finally {
				cursor.close();
			}
		} finally {
			db.close();
		}
		return statusMessage;
	}

	public void delete() {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		try {
			db.delete(TABLE_NAME, null, null);
		} finally {
			db.close();
		}
	}

	public SQLiteDatabase getReadableDatabase() {
		return dbHelper.getReadableDatabase();    	
	}

	public SQLiteDatabase getWritableDatabase() {
		return dbHelper.getWritableDatabase();    	
	}

	public void close() {
		this.dbHelper.close();
	}

	// DbHelper implementations
	public class DbHelper extends SQLiteOpenHelper {
		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, dbVersion);
		}

		public void onCreate(SQLiteDatabase db) {
			String sql = String.format(
					"create table %s (%s int primary key, %s VARCHAR, %s VARCHAR, %s VARCHAR, %s VARCHAR, %s int)",
					TABLE_NAME, C_ID, C_LATITUDE, C_LONGITUDE,
					C_MESSAGE, C_ADDRESS, C_CREATED_DATE);
			db.execSQL(sql);
		}

		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("drop table " + TABLE_NAME);
			this.onCreate(db);
		}
	}
}
